package it.univaq.disim.mwt.business.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import it.univaq.disim.mwt.business.AssenzeService;
import it.univaq.disim.mwt.business.model.Alunno;
import it.univaq.disim.mwt.business.model.Assenza;
import it.univaq.disim.mwt.business.model.RegistroDiClasse;
import it.univaq.disim.mwt.business.model.RegistroPersonale;
import it.univaq.disim.mwt.business.model.TipologiaLaboratorio;

public class JDBCAssenzaService implements AssenzeService{

	@Override
	public Assenza findAssenzeById(Long id) {
	
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		String sql="select * from assenze where id = ? ";
		Assenza assenze=null;
		
		try {
		
			connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setLong(1, id);
			resultSet = preparedStatement.executeQuery();			
			resultSet.next();
            
			Long ide = resultSet.getLong("id");
			Long idRegistroClasse = resultSet.getLong("id_registro_classe");
			Long idAlunno = resultSet.getLong("id_alunno");
			Date data = resultSet.getDate("data");
			Long idRegistroProf = resultSet.getLong("id_registro_professore");
			
			JDBCAlunnoService jdbcAlunnoService= new JDBCAlunnoService();
			Alunno alunno=jdbcAlunnoService.findAlunnoById(idAlunno);
			
			JDBCRegistroDiClasseService jdbcRegistroDiClasseService= new JDBCRegistroDiClasseService();
			RegistroDiClasse registriDiClasse=jdbcRegistroDiClasseService.findRegistroDiClasseByID(idRegistroClasse);
			
			JDBCRegistroPersonaleService jdbcRegistroPersonaleService= new JDBCRegistroPersonaleService();
			RegistroPersonale registroPersonale= jdbcRegistroPersonaleService.findRegistroPersonaleById(idRegistroProf);
			
			assenze = new Assenza(ide, registriDiClasse, alunno, data, registroPersonale);
			
			resultSet.close();
			preparedStatement.close();
			connection.close();
			
			return assenze ;
		
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
	
		return null;
			
	}

	@Override
	public List<Assenza> findAllAssenze() {
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		List<Assenza> list= new ArrayList<Assenza>();
		Assenza assenze=null;
		String sql=" select * from assenze ";
		
		try {
		
			connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();			
			
			while(resultSet.next()){
				
				Long ide = resultSet.getLong("id");
				Long idRegistroClasse = resultSet.getLong("id_registro_classe");
				Long idAlunno = resultSet.getLong("id_alunno");
				Date data = resultSet.getDate("data");
				Long idRegistroProf = resultSet.getLong("id_registro_professore");
				
				JDBCAlunnoService jdbcAlunnoService= new JDBCAlunnoService();
				Alunno alunno=jdbcAlunnoService.findAlunnoById(idAlunno);
				
				JDBCRegistroDiClasseService jdbcRegistroDiClasseService= new JDBCRegistroDiClasseService();
				RegistroDiClasse registriDiClasse=jdbcRegistroDiClasseService.findRegistroDiClasseByID(idRegistroClasse);
				
				JDBCRegistroPersonaleService jdbcRegistroPersonaleService= new JDBCRegistroPersonaleService();
				RegistroPersonale registroPersonale= jdbcRegistroPersonaleService.findRegistroPersonaleById(idRegistroProf);
				
				assenze = new Assenza(ide, registriDiClasse, alunno, data, registroPersonale);
				
	            list.add(assenze);
			}

			resultSet.close();
			preparedStatement.close();
			connection.close();
		
			return list;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}

}